﻿using System;
using System.Data;

using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;
//using Oracle.DataAccess.Types;

using LMD.Constant;
using LMD.Lib.DataClass;
using LMD.Lib.Oracle;

namespace DBQuery
{
    /// <summary>
    /// Summary description for EventData
    /// </summary>
    public static class EventTable
    {

        public static int SaveEvent(UFTData uftData)
        {
            DBLib dbl = new DBLib();
            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "out_new_id";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Direction = ParameterDirection.Output;
            
            OracleParameter prmCode = new OracleParameter();
            prmCode.ParameterName = "in_CODE";
            prmCode.OracleDbType = OracleDbType.VarChar;
            prmCode.Value = uftData.sxCode;

            OracleParameter prmEventDate = new OracleParameter();
            prmEventDate.ParameterName = "in_EVENT_DATE";
            prmEventDate.OracleDbType = OracleDbType.Date;
            prmEventDate.Value = uftData.dtmEventDate;

            OracleParameter prmCreatedBy = new OracleParameter();
            prmCreatedBy.ParameterName = "in_CREATE_BY";
            prmCreatedBy.OracleDbType = OracleDbType.VarChar;
            prmCreatedBy.Value = ICPUser.ID;
                                   
            OracleParameter prmInvitationAttachmentID = new OracleParameter();
            prmInvitationAttachmentID.ParameterName = "in_INVITATION_ATTACHMENT_ID";
            prmInvitationAttachmentID.OracleDbType = OracleDbType.Number;
            if (uftData.iInvitationAttachmentId < 1)
            {
                prmInvitationAttachmentID.Value = DBNull.Value;
            }
            else
            {
                prmInvitationAttachmentID.Value = uftData.iInvitationAttachmentId;
            }

            OracleParameter prmEventId = new OracleParameter();
            prmEventId.ParameterName = "in_P_EVENT_ID";
            prmEventId.OracleDbType = OracleDbType.Number;
            prmEventId.Value = uftData.iEventId;

            OracleParameter prmLocation = new OracleParameter();
            prmLocation.ParameterName = "in_LOCATION";
            prmLocation.OracleDbType = OracleDbType.VarChar;
            prmLocation.Value = uftData.sxLocation;

            OracleParameter prmStart = new OracleParameter();
            prmStart.ParameterName = "in_START_TIME";
            prmStart.OracleDbType = OracleDbType.Date;
            prmStart.Value = uftData.dtmStartTime;

            OracleParameter prmEnd = new OracleParameter();
            prmEnd.ParameterName = "in_END_TIME";
            prmEnd.OracleDbType = OracleDbType.Date;
            prmEnd.Value = uftData.dtmEndTime;
 
            String sxQuery = "pr_insert_t_relation_event";

            dbl.ExecuteProcedure(sxQuery, prmID, prmCode, prmEventDate, prmCreatedBy,
                prmInvitationAttachmentID, prmEventId, prmLocation, prmStart, prmEnd);
            
            return Convert.ToInt32(prmID.Value);
        }
        public static DataTable GetEventDataByID(int iEventID)
        {
            DBLib dbl = new DBLib();

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iEventID;

            String sxQuery = 
                @"
                select 
                    T_RELATION_EVENT_ID,
                    CODE,
                    EVENT_DATE,
                    INVITATION_ATTACHMENT_ID,
                    P_EVENT_ID,
                    EVENT_ATTACHMENT_ID,
                    LOCATION,
                    START_TIME,
                    END_TIME,
                    PRESENTATION_ID
                from 
                    T_RELATION_EVENT
                where
                    T_RELATION_EVENT_ID = :prmID";
            DataTable dt = dbl.GetDataTable(sxQuery,prmID);
            
            return dt;
        }

        public static DataTable GetEventLog(int iEventID)
        {
            DBLib dbl = new DBLib();

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iEventID;

            String sxQuery =
            @"
            select 
                t3.ORIGINAL_FILENAME, t3.T_FILE_UPLOAD_ID
            from 
                T_EVENT_LOG_UPLOAD t1
            left join
                T_RELATION_EVENT t2
                on t1.T_RELATION_EVENT_ID = t2.T_RELATION_EVENT_ID
            left join
                T_FILE_UPLOAD t3
                on t1.T_FILE_UPLOAD_ID = t3.T_FILE_UPLOAD_ID
            where t2.T_RELATION_EVENT_ID = :prmID";
            DataTable dt = dbl.GetDataTable(sxQuery, prmID);
            
            return dt;
        }

        public static bool CheckIsSubmitted(int iEventId)
        {
            DBLib dbl = new DBLib();
            int counter = 0;

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iEventId;
            
            String sxQuery =
            @"
            select 
                count(STATUS_SUBMIT)
            from 
                T_RELATION_INVITATION
            where 
                T_RELATION_EVENT_ID = :prmID 
            and 
                STATUS_SUBMIT IS NOT NULL";
            counter = Convert.ToInt32(dbl.GetSingleValue(sxQuery, prmID));
            
            return counter > 0;
        }

        public static bool CheckIsClosed(int iEventId)
        {
            DBLib dbl = new DBLib();
            int counter = 0;

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iEventId;

            String sxQuery =
            @"
            select 
                count(*)
            from 
                T_RELATION_EVENT 
            where 
                T_RELATION_EVENT_ID = :prmID 
            and 
                CLOSED_DATE is not null";
            counter = Convert.ToInt32(dbl.GetSingleValue(sxQuery, prmID));
            
            return counter != 0;
        }

        public static DataTable GetEventList(bool bSubmitted, bool bAdmin, bool bAdminForum)
        {
            DBLib dbl = new DBLib();
            OracleParameter prmUserId = new OracleParameter();
            prmUserId.ParameterName = "prmUserId";
            prmUserId.OracleDbType = OracleDbType.VarChar;
            prmUserId.Value = ICPUser.ID; 
            String sxQuery = String.Empty;
            DataTable dt = new DataTable();
            if (bAdmin || bAdminForum)
            {
                sxQuery = bSubmitted ?
                @"
                select
                    T_RELATION_EVENT_ID,
                    CODE,
                    EVENT_DATE,
                    STATUS_CLOSE,
                    STATUS_NEW,
                    STATUS_UNDANG,
                    STATUS_CONFIRM
                from
                (                    
                    select 
                        T_RELATION_EVENT_ID,
                        CODE,
                        EVENT_DATE,
                        STATUS_CLOSE,
                        case
                            when 
                                (
                                    (
                                        ( count(T_RELATION_EVENT_GUEST_ID) <= 0 ) OR 
                                        ( SUM(UNDANG) < count(T_RELATION_EVENT_GUEST_ID) )
                                    )
                                )     
                                and STATUS_CLOSE = 'N' 
                            then 'Y'
                            else 'N'
                        end STATUS_NEW,
                        STATUS_UNDANG,
                        STATUS_CONFIRM,
                        decode(STATUS_UNDANG, 'Y', 1, 0) UNDANG_COUNT,
                        max(decode(STATUS_UNDANG, 'Y', 1, 0)) over (partition by T_RELATION_EVENT_ID) UNDANG_MAX--,
                        --decode(STATUS_CONFIRM, 'Y', 1, 0) CONFIRM_COUNT,
                        --max(decode(STATUS_CONFIRM, 'Y', 1, 0)) over (partition by T_RELATION_EVENT_ID) CONFIRM_MAX
                    from
                        (
                            select                   
                                t1.T_RELATION_EVENT_ID,
                                t3.T_RELATION_EVENT_GUEST_ID,
                                t1.CODE,
                                t1.EVENT_DATE,
                                decode(t1.closed_date, null, 'N', 'Y') STATUS_CLOSE,
                                decode(t3.STATUS_UNDANG, 'Y', 1, 0) UNDANG,
                                t3.STATUS_UNDANG,
                                nvl(t1.SUBMIT_CONFIRM,'N') STATUS_CONFIRM
                                --decode(t2.STATUS_CONFIRM, null, 'N', 'Y') STATUS_CONFIRM            
                            from 
                                T_RELATION_EVENT t1
                                left join T_RELATION_INVITATION t2
                                    on t2.T_RELATION_EVENT_ID = t1.T_RELATION_EVENT_ID
                                left join T_RELATION_EVENT_GUEST t3
                                    on t2.T_RELATION_INVITATION_ID = t3.T_RELATION_INVITATION_ID    
                                left join V_ICP_CAR v1
                                    on t2.CUSTOMER_ID = v1.COMPANY_ID        
                            where 
                                STATUS_SUBMIT IS NOT NULL
                        )                
                    group by 
                        T_RELATION_EVENT_ID,
                        CODE,
                        EVENT_DATE,
                        STATUS_CLOSE,
                        STATUS_UNDANG,
                        STATUS_CONFIRM
                )
                where UNDANG_COUNT = UNDANG_MAX
                --and CONFIRM_COUNT = CONFIRM_MAX
                order by
                    EVENT_DATE desc"
                :
                @"select distinct 
                    b.T_RELATION_EVENT_ID, 
                    b.CODE, 
                    b.EVENT_DATE, 
                    '' STATUS_CLOSE, 
                    '' STATUS_NEW,
                    '' STATUS_UNDANG, 
                    '' STATUS_CONFIRM
                from 
                    T_RELATION_EVENT b 
                    left join T_RELATION_INVITATION a 
                        on a.T_RELATION_EVENT_ID = b.T_RELATION_EVENT_ID
                where 
                    a.STATUS_SUBMIT is null
                order by 
                    b.EVENT_DATE desc";
                dt = dbl.GetDataTable(sxQuery);
                
                return dt;
            }
            else
            {
                sxQuery = bSubmitted ?
                @"
                select
                    T_RELATION_EVENT_ID,
                    CODE,
                    EVENT_DATE,
                    STATUS_CLOSE,
                    STATUS_NEW,
                    STATUS_UNDANG,
                    STATUS_CONFIRM
                from
                (   
                    select 
                        T_RELATION_EVENT_ID,
                        CODE,
                        EVENT_DATE,
                        STATUS_CLOSE,
                        case
                            when 
                                (
                                    (
                                        ( count(T_RELATION_EVENT_GUEST_ID) <= 0 ) OR 
                                        ( SUM(UNDANG) < count(T_RELATION_EVENT_GUEST_ID) )
                                    )
                                )     
                                and STATUS_CLOSE = 'N' then 'Y'
                            else 'N'
                        end STATUS_NEW,
                        STATUS_UNDANG,
                        STATUS_CONFIRM,
                        decode(STATUS_UNDANG, 'Y', 1, 0) UNDANG_COUNT,
                        max(decode(STATUS_UNDANG, 'Y', 1, 0)) over (partition by T_RELATION_EVENT_ID) UNDANG_MAX--,
                        --decode(STATUS_CONFIRM, 'Y', 1, 0) CONFIRM_COUNT,
                        --max(decode(STATUS_CONFIRM, 'Y', 1, 0)) over (partition by T_RELATION_EVENT_ID) CONFIRM_MAX
                    from
                        (
                            select                   
                                t1.T_RELATION_EVENT_ID,
                                t3.T_RELATION_EVENT_GUEST_ID,
                                t1.CODE,
                                t1.EVENT_DATE,
                                decode(t1.closed_date, null, 'N', 'Y') STATUS_CLOSE,
                                decode(t3.STATUS_UNDANG, 'Y', 1, 0) UNDANG,
                                t3.STATUS_UNDANG,
                                nvl(t1.SUBMIT_CONFIRM,'N') STATUS_CONFIRM
                                --decode(t2.STATUS_CONFIRM, null, 'N', 'Y') STATUS_CONFIRM            
                            from 
                                T_RELATION_EVENT t1
                                left join T_RELATION_INVITATION t2
                                    on t2.T_RELATION_EVENT_ID = t1.T_RELATION_EVENT_ID
                                left join T_RELATION_EVENT_GUEST t3
                                    on t2.T_RELATION_INVITATION_ID = t3.T_RELATION_INVITATION_ID    
                                left join V_ICP_CAR v1
                                    on t2.CUSTOMER_ID = v1.COMPANY_ID        
                            where 
                                STATUS_SUBMIT IS NOT NULL 
                                and v1.EMPLOYEE_NO IN
                                (
                                    SELECT DISTINCT NIK
                                    FROM 
                                    v_icp_escalation
                                    START WITH NIK = :prmUserId        
                                    CONNECT BY PRIOR t_qrtz_user_destination_id = parent_id
                                )
                        )                
                    group by 
                        T_RELATION_EVENT_ID,
                        CODE,
                        EVENT_DATE,
                        STATUS_CLOSE,
                        STATUS_UNDANG,
                        STATUS_CONFIRM
                )
                where 
                    UNDANG_COUNT = UNDANG_MAX
                    --and CONFIRM_COUNT = CONFIRM_MAX
                order by
                    EVENT_DATE desc"
                :
                @"select distinct 
                    b.T_RELATION_EVENT_ID, 
                    b.CODE, b.EVENT_DATE, 
                    '' STATUS_CLOSE, 
                    '' STATUS_NEW,
                    '' STATUS_UNDANG, 
                    '' STATUS_CONFIRM
                from 
                    T_RELATION_EVENT b 
                    left join T_RELATION_INVITATION a 
                        on a.T_RELATION_EVENT_ID = b.T_RELATION_EVENT_ID
                    left join V_ICP_CAR d
                        on a.CUSTOMER_ID = d.COMPANY_ID
                where 
                    a.STATUS_SUBMIT is null
                    and d.EMPLOYEE_NO IN
                    (
                        select :prmUserId from dual
						UNION ALL
						SELECT DISTINCT NIK
                        FROM 
                        v_icp_escalation
                        START WITH NIK = :prmUserId        
                        CONNECT BY PRIOR t_qrtz_user_destination_id = parent_id
                    )
                order by 
                    b.EVENT_DATE desc";
                dt = dbl.GetDataTable(sxQuery, prmUserId);
                
                return dt;
            }
        }
		
        public static void CloseEvent(UFTData uftData)
        {
            DBLib dbl = new DBLib();

            OracleParameter prmEventId = new OracleParameter();
            prmEventId.ParameterName = "prmEventId";
            prmEventId.OracleDbType = OracleDbType.Number;
            prmEventId.Value = uftData.iEventId;

            OracleParameter prmEventAttachmentId = new OracleParameter();
            prmEventAttachmentId.ParameterName = "prmEventAttachmentId";
            prmEventAttachmentId.OracleDbType = OracleDbType.Number;
            prmEventAttachmentId.Value = uftData.iEventAttachmentId;

            OracleParameter prmUserId = new OracleParameter();
            prmUserId.ParameterName = "prmUserId";
            prmUserId.OracleDbType = OracleDbType.VarChar;
            prmUserId.Value = ICPUser.ID;

            OracleParameter prmPresentationId = new OracleParameter();
            prmPresentationId.ParameterName = "prmPresentationId";
            prmPresentationId.OracleDbType = OracleDbType.Number;
            prmPresentationId.Value = uftData.iPresentationId;

            String sxQuery =
                @"
                update
                    T_RELATION_EVENT
                set
                    CLOSED_DATE = sysdate,
                    CLOSED_BY = :prmUserId,
                    EVENT_ATTACHMENT_ID = :prmEventAttachmentId,
                    PRESENTATION_ID = :prmPresentationId
                where
                    T_RELATION_EVENT_ID = :prmEventId";

            dbl.ExecuteNonQuery(sxQuery, prmUserId, prmEventAttachmentId, prmPresentationId, prmEventId);
            
        }

        public static DataTable GetClosedEvent()
        {
            DBLib dbl = new DBLib();
            String sxQuery = 
                @"
                select 
                    T_RELATION_EVENT_ID,
                    CODE,
                    EVENT_DATE,
                    STATUS_CLOSE,
                    case
                        when 
                            (
                                (
                                    ( count(T_RELATION_EVENT_GUEST_ID) <= 0 ) OR 
                                    ( SUM(UNDANG) < count(T_RELATION_EVENT_GUEST_ID) )
                                )
                            )     
                            and STATUS_CLOSE = 'N' 
                        then 'Y'
                        else 'N'
                    end STATUS_NEW
                from
                    (
                        select                   
                            t1.T_RELATION_EVENT_ID,
                            t3.T_RELATION_EVENT_GUEST_ID,
                            t1.CODE,
                            t1.EVENT_DATE,
                            decode(t1.closed_date, null, 'N', 'Y') STATUS_CLOSE,
                            decode(t3.STATUS_UNDANG, 'Y', 1, 0) UNDANG            
                        from 
                            T_RELATION_EVENT t1
                            left join T_RELATION_INVITATION t2
                                on t2.T_RELATION_EVENT_ID = t1.T_RELATION_EVENT_ID
                            left join T_RELATION_EVENT_GUEST t3
                                on t2.T_RELATION_INVITATION_ID = t3.T_RELATION_INVITATION_ID    
                            left join V_ICP_CAR v1
                                on t2.CUSTOMER_ID = v1.COMPANY_ID        
                        where STATUS_SUBMIT IS NOT NULL
                    )     
                where 
                    STATUS_CLOSE = 'Y'
                group by 
                    T_RELATION_EVENT_ID,
                    CODE,
                    EVENT_DATE,
                    STATUS_CLOSE
                order by 
                    EVENT_DATE desc";
            DataTable dt = dbl.GetDataTable(sxQuery);
            
            return dt;
        }

        public static int SaveEventLog(DBLib dbl, EventLogData evlData)
        {
            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "out_new_id";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Direction = ParameterDirection.Output;

            OracleParameter prmFileUploadId = new OracleParameter();
            prmFileUploadId.ParameterName = "in_T_FILE_UPLOAD_ID";
            prmFileUploadId.OracleDbType = OracleDbType.Number;
            prmFileUploadId.Value = evlData.iFileUploadId;

            OracleParameter prmEventId = new OracleParameter();
            prmEventId.ParameterName = "in_T_RELATION_EVENT_ID";
            prmEventId.OracleDbType = OracleDbType.Number;
            prmEventId.Value = evlData.iEventId;

            OracleParameter prmCreateBy = new OracleParameter();
            prmCreateBy.ParameterName = "in_CREATE_BY";
            prmCreateBy.OracleDbType = OracleDbType.VarChar;
            prmCreateBy.Value = ICPUser.ID;

            String sxQuery = "pr_insert_t_event_log_upload";

            dbl.ExecuteProcedure(sxQuery, prmID, prmFileUploadId, prmEventId, prmCreateBy);
            
            return Convert.ToInt32(prmID.Value);
        }



        public static DataTable GetEventDataForEmail(int iEventID)
        {
            DBLib dbl = new DBLib();

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iEventID;

            String sxQuery =
                @"
                select
                    t2.T_RELATION_EVENT_ID,
                    t2.CODE,
                    p1.CODE EVENT,
                    t2.P_EVENT_ID,
                    t2.EVENT_DATE,
                    t2.START_TIME,
                    t2.END_TIME,
                    t2.LOCATION,
                    t2.INVITATION_ATTACHMENT_ID,
                    t3.ORIGINAL_FILENAME,
                    t3.PHYSICAL_FILENAME,
                    p2.PATH
                from
                (
                    select
                        T_RELATION_EVENT_ID,
                        CODE,
                        EVENT_DATE,
                        START_TIME,
                        END_TIME,
                        LOCATION,
                        INVITATION_ATTACHMENT_ID,
                        P_EVENT_ID
                    from
                        T_RELATION_EVENT
                    where
                        T_RELATION_EVENT_ID = :prmID
                ) t2
                left join
                    P_EVENT p1
                on
                    t2.P_EVENT_ID = p1.P_EVENT_ID
                left join
                    T_FILE_UPLOAD t3
                on
                    T2.INVITATION_ATTACHMENT_ID = T3.T_FILE_UPLOAD_ID
                left join 
                    P_FILE_UPLOAD_TYPE p2
                on 
                    t3.P_FILE_UPLOAD_TYPE_ID = p2.P_FILE_UPLOAD_TYPE_ID
                    and 
                    t3.T_FILE_UPLOAD_ID = t2.INVITATION_ATTACHMENT_ID
                    and 
                    nvl(t3.IS_DELETED, 'N') <> 'Y'";
            DataTable dt = dbl.GetDataTable(sxQuery, prmID);

            return dt;
        }
        public static DataTable GetCARAndCPForEmail(int iEventID)
        {
            DBLib dbl = new DBLib();

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iEventID;

            String sxQuery =
                @"
                select
                    t1.T_RELATION_INVITATION_ID,
                    t1.CUSTOMER_ID,
                    v1.NAME,
                    v2.CAR_EMPLOYEE_NO,
                    v2.CAR_NAME,
                    v2.COMPANY_NAME
                from 
                (
                    select
                        T_RELATION_INVITATION_ID,
                        CUSTOMER_ID,
                        T_RELATION_EVENT_ID
                    from
                        T_RELATION_INVITATION
                    where T_RELATION_EVENT_ID = :prmID
                ) t1
                left join
                    T_RELATION_EVENT_GUEST t4
                    on t1.T_RELATION_INVITATION_ID = t4.T_RELATION_INVITATION_ID
                left join V_ICP_CUSTOMER_CONTACT v1
                    on (t4.CONTACT_PERSON_ID = v1.V_CUSTOMER_CONTACT_ID and v1.IS_NEW_CP = 'N')
                    or (t4.NEW_CONTACT_PERSON_ID = v1.V_CUSTOMER_CONTACT_ID and v1.IS_NEW_CP = 'Y')
                left join V_ICP_COMPANY_CAR_SALES v2
                    on t1.CUSTOMER_ID = v2.CUSTOMER_ID";
            DataTable dt = dbl.GetDataTable(sxQuery, prmID);

            return dt;
        }
        public static DataTable GetAssManForEmail(int iCARId)
        {
            DBLib dbl = new DBLib();

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iCARId;

            String sxQuery =
                @"
                select
                    t1.NIK,
                    emp.EMPLOYEE_NAME NAME,
                    emp.E_MAIL_ADDR EMAIL
                from
                (
                    select to_char(:prmID) NIK from dual
                    UNION ALL
                    SELECT DISTINCT to_char(NIK)
                    FROM 
                    v_icp_escalation
                    START WITH NIK = :prmID
                    CONNECT BY PRIOR parent_id = t_qrtz_user_destination_id
                ) t1
                left join
                    slogix.employee emp
                on t1.NIK = emp.EMPLOYEE_NO";
            DataTable dt = dbl.GetDataTable(sxQuery, prmID);

            return dt;
        }
		public static String GetHTMLEmail(int iEventId)
        {
            DBLib dbl = new DBLib();

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iEventId;

            String sxQuery =
                @"
                select
                    HTML_MAIL_BODY
                from
                    T_RELATION_EVENT
                where
                    T_RELATION_EVENT_ID = :prmID";
            DataTable dt = dbl.GetDataTable(sxQuery, prmID);
            String sxOutput = dt.Rows[0]["HTML_MAIL_BODY"].ToString();

            return sxOutput;
        }
        public static String GetPlainEmail(int iEventId)
        {
            DBLib dbl = new DBLib();

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iEventId;

            String sxQuery =
                @"
                select
                    PLAIN_MAIL_BODY
                from
                    T_RELATION_EVENT
                where
                    T_RELATION_EVENT_ID = :prmID";
            DataTable dt = dbl.GetDataTable(sxQuery, prmID);
            String sxOutput = dt.Rows[0]["PLAIN_MAIL_BODY"].ToString();

            return sxOutput;
        }
        public static void SetHTMLEmail(EmailBodyData ebd)
        {
            DBLib dbl = new DBLib();

            OracleParameter prmValue = new OracleParameter();
            prmValue.ParameterName = "prmValue";
            prmValue.OracleDbType = OracleDbType.Clob;
            prmValue.Value = ebd.sxHTMLEmail;

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = ebd.iEventId;

            String sxQuery =
                @"
                update
                    T_RELATION_EVENT
                set
                    HTML_MAIL_BODY = :prmValue
                where
                    T_RELATION_EVENT_ID = :prmID";
            dbl.ExecuteNonQuery(sxQuery, prmValue, prmID);
        }
        public static void SetPlainEmail(EmailBodyData ebd)
        {
            DBLib dbl = new DBLib();

            OracleParameter prmValue = new OracleParameter();
            prmValue.ParameterName = "prmValue";
            prmValue.OracleDbType = OracleDbType.Clob;
            prmValue.Value = ebd.sxPlainEmail;

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = ebd.iEventId;

            String sxQuery =
                @"
                update
                    T_RELATION_EVENT
                set
                    PLAIN_MAIL_BODY = :prmValue
                where
                    T_RELATION_EVENT_ID = :prmID";
            dbl.ExecuteNonQuery(sxQuery, prmValue, prmID);
        }
    }
}